from py2neo import Graph

graph = Graph("http://localhost:7474", user="neo4j", password="12345678", name='bilibili.db')


def import_up_attributes(file_path):
    query = f'''
        LOAD CSV WITH HEADERS FROM "{file_path}" AS line
        WITH line, REPLACE(line.头像, 'null', 'https://p1.ssl.qhimg.com/t01cf7f93a00e61e5e9.jpg') AS up_profile
        WHERE line.up主 IS NOT NULL
        MERGE (u:UP主{{name: line.up主}})
        ON CREATE SET u.link = line.主页链接,
                      u.fans = line.粉丝数,
                      u.profile = up_profile
    '''
    graph.run(query)


def creation(file_path):
    query = f'''
        LOAD CSV WITH HEADERS FROM "{file_path}" AS line
        WITH line, REPLACE(line.视频名, '【', '[') AS video_name
        WITH line, REPLACE(video_name, '】', ']') AS video_name
        WITH line, REPLACE(video_name, '/', '|') AS video_name
        WITH line, video_name, REPLACE(line.图片链接, '', 'https://p1.ssl.qhimg.com/t01cf7f93a00e61e5e9.jpg') AS pic_link
        MERGE (v:视频{{name: video_name}})
        ON CREATE SET v.pic_link = pic_link,
                      v.vid_link = line.视频链接,
                      v.views = line.播放量,
                      v.barrage = line.弹幕数,
                      v.likes = line.点赞数,
                      v.coins = line.投币数,
                      v.favorites = line.收藏数
        WITH v, line
        UNWIND split(line.UP主,'/') AS up_name
        MERGE (u:UP主{{name: up_name}})
        ON CREATE SET u.name = up_name
        MERGE (u)-[:创作]->(v)
        WITH v, line
        UNWIND split(line.标签,'/') AS tag_name
        MERGE (t:标签{{name: tag_name}})
        ON CREATE SET t.name = tag_name
        MERGE (v)-[:拥有标签]->(t)
    '''
    graph.run(query)


# 导入数据库
# import_up_attributes("file:///upinfo01.csv")
# creation("file:///videoinfo01.csv")


# 查询某个视频的所有信息
def get_video_info(video_name):
    query = f'''
        MATCH (video:视频{{name: "{video_name}"}})
        OPTIONAL MATCH (video)<-[:创作]-(up:UP主)
        OPTIONAL MATCH (video)-[:拥有标签]->(tag:标签)
        RETURN video.name AS video_name, video.pic_link AS pic_link, video.vid_link AS vid_link,
               video.views AS views, video.barrage AS barrage, video.likes AS likes,
               video.coins AS coins, video.favorites AS favorites,
               collect(DISTINCT up.name) AS up_names, collect(DISTINCT tag.name) AS tag_names,collect(DISTINCT up.profile) AS up_heads
    '''
    result = graph.run(query).data()

    return result[0]


# 查询拥有某个标签的所有视频并列出播放量最高的前十条的名字和播放量
def get_top_videos_by_tag(tag_name):
    query = f'''
        MATCH (t:标签{{name: "{tag_name}"}})<-[:拥有标签]-(v:视频)
        RETURN v.name AS video_name,v.pic_link AS pic_link,v.views AS video_views
        ORDER BY
            CASE
                WHEN v.views ENDS WITH "万" THEN toInteger(REPLACE(v.views, "万", "")) * 10000
                ELSE toInteger(v.views)
            END DESC
        LIMIT 50
    '''
    result = graph.run(query).data()
    return result


# 基于标签、UP主、播放量对某个视频列出十个相关推荐视频
def get_related_videos(video_name, num=8):
    # 获取给定视频的标签、UP主和播放量
    query = f'''
        MATCH (v:视频{{name: "{video_name}"}})
        OPTIONAL MATCH (v)-[:拥有标签]->(tag:标签)
        WITH v, collect(tag.name) AS tags
        OPTIONAL MATCH (v)<-[:创作]-(up:UP主)
        WITH v, tags, collect(up.name) AS up_names
        OPTIONAL MATCH (v)
        RETURN v.name AS video_name, tags, up_names, v.views AS views, v.vid_link AS video_link, v.pic_link AS pic_link
    '''
    result = graph.run(query).data()
    if not result:
        print(f"视频 '{video_name}' 不存在或没有相关信息。")
        return []

    video_tags = set(result[0]['tags'])
    video_up_names = set(result[0]['up_names'])
    if result[0]['views'].endswith("万"):
        video_views = result[0]['views'].replace("万", "")
        if video_views.replace(".", "").isdigit():
            video_views = float(video_views) * 10000
    elif result[0]['views'].replace(".", "").isdigit():
        video_views = float(result[0]['views'])

    # 计算其他视频的相似度并加权
    query = f'''
        MATCH (v:视频)
        WHERE v.name <> "{video_name}"
        OPTIONAL MATCH (v)-[:拥有标签]->(tag:标签)
        WITH v, collect(tag.name) AS tags
        OPTIONAL MATCH (v)<-[:创作]-(up:UP主)
        WITH v, tags, collect(up.name) AS up_names
        OPTIONAL MATCH (v)
        RETURN v.name AS video_name, tags, up_names, v.views AS views, v.vid_link AS video_link, v.pic_link AS pic_link
    '''
    result = graph.run(query).data()

    recommendations = []
    for item in result:
        common_tags = set(item['tags']) & video_tags
        common_up_names = set(item['up_names']) & video_up_names

        # 根据标签和UP主的相似度进行加权
        tag_similarity = len(common_tags) / max(len(video_tags), 1)
        up_similarity = len(common_up_names) / max(len(video_up_names), 1)

        # 检查播放量值是否只包含数字和小数点，如果不满足条件则跳过播放量相似度计算
        item_views = item['views']
        if item_views.endswith("万"):
            item_views = item_views.replace("万", "")
            if not item_views.replace(".", "").isdigit():
                continue
            item_views = float(item_views) * 10000
        elif item_views.replace(".", "").isdigit():
            item_views = float(item_views)
        else:
            continue

        view_difference = abs(item_views - video_views)
        if view_difference > 0:
            view_similarity = 1 / view_difference
        else:
            view_similarity = 1  # 避免除以零

        # 综合相似度为权重相加
        weighted_similarity = 0.4 * tag_similarity + 0.4 * up_similarity + 0.2 * view_similarity

        recommendations.append({
            'video_name': item['video_name'],
            'up_names': item['up_names'],
            'views': item['views'],
            'video_link': item['video_link'],
            'pic_link': item['pic_link'],
            'weighted_similarity': weighted_similarity  # 将相似度输出为百分比形式
        })

    # 根据加权相似度降序排序，得到最终推荐结果
    recommendations.sort(key=lambda x: x['weighted_similarity'], reverse=True)
    return recommendations[:num]


# 基于标签、播放量对某个视频列出十个相关推荐UP主
def get_similar_ups(video_name):
    abs_threshold = 10000
    while True:
        query = f'''
            MATCH (video:视频{{name: "{video_name}"}})-[:拥有标签]->(tag:标签)
            WITH tag, video,
                 CASE
                    WHEN video.views ENDS WITH "万" THEN toInteger(REPLACE(video.views, "万", "")) * 10000
                    ELSE toInteger(video.views)
                 END AS adjusted_views
            MATCH (t:标签{{name: tag.name}})<-[:拥有标签]-(v:视频)
            WITH tag, v, 
                 CASE
                    WHEN v.views ENDS WITH "万" THEN toInteger(REPLACE(v.views, "万", "")) * 10000
                    ELSE toInteger(v.views)
                 END AS adjusted_similar_views,
                 adjusted_views
            WHERE ABS(adjusted_similar_views - adjusted_views) <= {abs_threshold} AND v.name <> "{video_name}"
            MATCH (up:UP主)-[:创作]->(v)
            WITH DISTINCT up.name AS up_name
            MATCH (u:UP主{{name: up_name}})
            RETURN  
                   u.name AS up_name,
                   u.fans AS up_fans,
                   u.profile AS up_head,
                   u.link AS up_link
            LIMIT 3
        '''
        result = graph.run(query).data()
        if len(result) >= 10 or abs_threshold > 100000000:
            return result
        else:
            abs_threshold *= 10


# 根据关键字搜索up主（3个）及视频
def find_videos_by_keyword(keyword):
    query = f'''
        MATCH (video:视频)
        WHERE video.name CONTAINS "{keyword}"
        RETURN video.name AS video_name,video.pic_link AS pic_link,video.views AS video_views
    '''
    result = graph.run(query).data()

    creator_query = f'''
        MATCH (creator:UP主)-[:创作]->(video:视频)
        WHERE creator.name CONTAINS "{keyword}"
        RETURN DISTINCT creator.name AS creator_name
        LIMIT 3
    '''
    creator_result = graph.run(creator_query).data()
    creator_names = [item['creator_name'] for item in creator_result]

    creator_video_names = {}
    creator_video_result = []
    for creator_name in creator_names:
        creator_video_query = f'''
            MATCH (creator:UP主{{name: "{creator_name}"}})-[:创作]->(video:视频)
            RETURN DISTINCT video.name AS video_name,video.pic_link AS pic_link,creator.name AS up_name,creator.profile AS head_link,video.views AS video_views,creator.link AS up_space_link 
            LIMIT 1
        '''
        creator_video_result += graph.run(creator_video_query).data()
    if not result:
        if not creator_video_result:
            return '1', '1'

    return result, creator_video_result

#查找某up的视频
def get_videos_by_up(up_name):
    query = f'''
        MATCH (up:UP主{{name: "{up_name}"}})-[:创作]->(v:视频)
        RETURN v.name AS video_name,v.pic_link AS pic_link,v.views AS video_views
    '''
    result = graph.run(query).data()
    return result


# 查询视频的热门标签的平均播放量，使用频率，弹幕数的平均数，点赞数与播放量的比的平均数，投币数与播放量的比的平均数，收藏数与播放量的比的平均数
def get_tags_sorted_by_avg_views(video_name):
    query = f'''
        MATCH (v:视频{{name: "{video_name}"}})-[:拥有标签]->(t:标签)
        WITH t, v,
        CASE
            WHEN v.views ENDS WITH "万" THEN toFloat(REPLACE(v.views, "万", "")) * 10000
            ELSE toFloat(v.views)
        END AS adjusted_views,
        CASE
            WHEN v.barrage ENDS WITH "万" THEN toFloat(REPLACE(v.barrage, "万", "")) * 10000
            ELSE toFloat(v.barrage)
        END AS adjusted_barrage,
        CASE
            WHEN v.likes ENDS WITH "万" THEN toFloat(REPLACE(v.likes, "万", "")) * 10000
            ELSE toFloat(v.likes)
        END AS adjusted_likes,
        CASE
            WHEN v.coins ENDS WITH "万" THEN toFloat(REPLACE(v.coins, "万", "")) * 10000
            ELSE toFloat(v.coins)
        END AS adjusted_coins,
        CASE
            WHEN v.favorites ENDS WITH "万" THEN toFloat(REPLACE(v.favorites, "万", "")) * 10000
            ELSE toFloat(v.favorites)
        END AS adjusted_favorites
        RETURN t.name AS tag,
               COUNT(v) AS video_count,
               toFloat(AVG(adjusted_views)) AS avg_views,
               toFloat(AVG(adjusted_barrage)) AS avg_barrage,
               toFloat(AVG(adjusted_likes / adjusted_views)) AS avg_likes_to_views_ratio,
               toFloat(AVG(adjusted_coins / adjusted_views)) AS avg_coins_to_views_ratio,
               toFloat(AVG(adjusted_favorites / adjusted_views)) AS avg_favorites_to_views_ratio
        ORDER BY avg_views DESC
        LIMIT 5
    '''
    result = graph.run(query).data()
    return result


import csv
#导出数据库
def export(csv_file_path):
    query = '''
        MATCH (u:UP主)-[:创作]->(v:视频)-[:拥有标签]->(t:标签)
        RETURN u.name AS up_name, u.fans AS up_fans, v.name AS video_name,
               v.views AS video_views, v.barrage AS video_barrage,
               v.likes AS video_likes, v.coins AS video_coins,
               v.favorites AS video_favorites, t.name AS tag_name
    '''
    result = graph.run(query).data()

    with open(csv_file_path, mode='w', newline='', encoding='utf-8') as csvfile:
        fieldnames = [
            'up_name', 'up_fans', 'video_name', 'video_views',
            'video_barrage', 'video_likes', 'video_coins',
            'video_favorites', 'tag_name'
        ]
        writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

        writer.writeheader()
        for item in result:
            writer.writerow(item)

# 调用函数并传入CSV文件路径
# csv_file_path = "ups_videos_and_tags.csv"
# export(csv_file_path)
# print(f"已导出！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！")